#这段代码的主要目的是从指定目录中读取所有的.po文件（通常用于软件本地化），解析这些文件，并将解析出的字符串存储在MySQL数据库中。这可以用于管理和存储多语言版本的字符串，特别适用于多语言软件的开发和维护。
import os
import polib
import platform
from sqlalchemy import create_engine, text

db_user = 'root'
db_password = '123'
db_host = 'localhost'
db_port = '3306'
db_name = 'scada'

# 创建到MySQL的连接
engine = create_engine(f'mysql+mysqlconnector://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}')


# 指定要遍历的.po文件的目录
# 根据操作系统决定要遍历的.po文件的目录
if platform.system() == 'Windows':
    directory = "C:\\Users\\jan\\Documents\\Project\\CproScada\\cpro_scada\\dist\\translations\\"  # 当前目录下的po文件夹
elif platform.system() == 'Linux':
    directory = '/tmp/po'  # /tmp/po文件夹
else:
    exit(1)

# 遍历指定目录下的所有文件
for file in os.listdir(directory):
    filename = os.fsdecode(file)
    # 如果文件是.po文件
    if filename.endswith('.po'):
        # 解析.po文件
        po = polib.pofile(os.path.join(directory, filename))
        # po = polib.pofile(directory + filename)
        
        # 根据文件名决定数据要插入到哪个列
        if 'English' in filename or 'en' in filename:
            column = 'en'
        elif 'Russian' in filename or 'ru' in filename:
            column = 'ru'
        else:
            continue  # 如果文件名不含指定的字符串，跳过这个文件

        # 准备要插入的数据
        data = []
        for entry in po:
            # 跳过msgid为None的条目
            if entry.msgid is None:
                print(entry)
                continue

            data.append({'k': entry.msgid, column: entry.msgstr})

        # 插入到MySQL
        insert_query = text(f"INSERT INTO ml (k, {column}) VALUES (:k, :{column}) ON DUPLICATE KEY UPDATE {column} = VALUES({column})")
        with engine.connect() as connection:
            for row in data:
                connection.execute(insert_query, **row)
